package su.theravada.jpalireader.openBookDlg;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Locale;
import java.util.ResourceBundle;

import javax.swing.ComboBoxModel;
import javax.swing.DefaultComboBoxModel;
import javax.swing.JComboBox;
import javax.swing.JLabel;
import javax.swing.SpinnerNumberModel;

import javax.swing.JPanel;
import javax.swing.JSpinner;
import javax.swing.SpinnerListModel;

import su.theravada.jpalireader.MainWindow;
import su.theravada.jpalireader.entities.CollectionInfo;
import su.theravada.jpalireader.util.SqlUtil;
import su.theravada.jpalireader.util.UTF8Control;

/**
* This code was edited or generated using CloudGarden's Jigloo
* SWT/Swing GUI Builder, which is free for non-commercial
* use. If Jigloo is being used commercially (ie, by a corporation,
* company or business for any purpose whatever) then you
* should purchase a license for each developer using Jigloo.
* Please visit www.cloudgarden.com for details.
* Use of Jigloo implies acceptance of these licensing terms.
* A COMMERCIAL LICENSE HAS NOT BEEN PURCHASED FOR
* THIS MACHINE, SO JIGLOO OR THIS CODE CANNOT BE USED
* LEGALLY FOR ANY CORPORATE OR COMMERCIAL PURPOSE.
*/
public class OpenBookDlgTab3 extends JPanel {
	private JComboBox ctlPTSBook;
	private JLabel ctlVolumeLabel;
	private JSpinner ctlPageNumber;
	private JLabel ctlPTSBookLabel;
	private JLabel ctlPageLabel;
	private JComboBox ctlVolume;
	private int _NodeID;
	
	public OpenBookDlgTab3()
	{
		super();
		initGUI();
		FillData();
	}
	
	public OpenBookDlgTab3(int NodeID) {
		super();
		initGUI();
		
		_NodeID=NodeID;
        PreparedStatement ps = null;
        ResultSet rs = null;
        
        ctlPTSBook.setVisible(false);
        ctlPTSBookLabel.setVisible(false);
        
        try
        {
			ps=	MainWindow.getDBConnection().prepareStatement("SELECT PTSPages.VolumeID, PTSPages.PageMin, PTSPages.PageMax "+
					"FROM PTSPages INNER JOIN "+
					"Files ON PTSPages.FileID = Files.FileID "+
					"INNER JOIN Nodes ON Files.FileID=Nodes.FileID "+
					"WHERE (Nodes.NodeID = ?)");
			ps.setInt(1, NodeID);
			
			rs=ps.executeQuery();
			if(rs.next())
			{
				ctlVolume.setEnabled(true);
				ctlPageNumber.setEnabled(true);
				
				ArrayList<VolumeInfo> arrVolumes=new ArrayList<VolumeInfo>();
				arrVolumes.add(new VolumeInfo(rs.getInt("VolumeID"),rs.getInt("PageMin"),rs.getInt("PageMax")));
				
				while(rs.next())				
					arrVolumes.add(new VolumeInfo(rs.getInt("VolumeID"),rs.getInt("PageMin"),rs.getInt("PageMax")));
				
				ComboBoxModel ctlVolumeModel = new DefaultComboBoxModel(arrVolumes.toArray());
				ctlVolume.setModel(ctlVolumeModel);
				ctlVolume.setSelectedIndex(0);
				if(ctlVolumeModel.getSize()==1)
					ctlVolume.setEnabled(false);				
			}
			else
			{
				ctlVolume.setEnabled(false);
				ctlPTSBook.setEnabled(false);
				ctlPageNumber.setEnabled(false);
			}
				
        }
		catch(SQLException sqle)
		{		
			SqlUtil.ReportSQLException(sqle);
		}
		catch(Exception ex)
		{		
			ex.printStackTrace();					
		}
		finally
		{
			SqlUtil.ClosePreparedStatement(ps);
			SqlUtil.CloseResultSet(rs);		
		}
		
	}
	
	private void FillData()
	{
		ResultSet rsParents = null;
		ResultSet rsChildren = null;
        PreparedStatement psSelectParents = null;
        PreparedStatement psSelectChildren = null;
    	ArrayList<CollectionInfo> arrCollections=new ArrayList<CollectionInfo>();
    	
        try
        {
        	psSelectParents=MainWindow.getDBConnection().prepareStatement("SELECT collectionid,title FROM collections WHERE parentid IS NULL AND hasptspages=true");
        	rsParents=psSelectParents.executeQuery();
        	
        	psSelectChildren=MainWindow.getDBConnection().prepareStatement("SELECT collectionid,title,hasptspages FROM collections WHERE parentid=? AND hasptspages=true");
        	
        	while(rsParents.next())
        	{
        		arrCollections.add(new CollectionInfo(rsParents.getInt("collectionid"),
        				rsParents.getString("title"),false));
        		
        		psSelectChildren.setInt(1, rsParents.getInt("collectionid"));
        		rsChildren=psSelectChildren.executeQuery();
        		while(rsChildren.next())
        		{
            		arrCollections.add(new CollectionInfo(rsChildren.getInt("collectionid"),
            				rsChildren.getString("title"),rsChildren.getBoolean("hasptspages")));        			
        		}
        		
        		SqlUtil.CloseResultSet(rsChildren);
        	}     
        	
        	ComboBoxModel ctlSelectCollectionModel = 
        		new DefaultComboBoxModel(arrCollections.toArray());
        	ctlPTSBook.setModel(ctlSelectCollectionModel);
		}
		catch(SQLException sqle)
		{		
			SqlUtil.ReportSQLException(sqle);
		}
		catch(Exception ex)
		{		
			ex.printStackTrace();					
		}
		finally
		{
			SqlUtil.ClosePreparedStatement(psSelectParents);
			SqlUtil.CloseResultSet(rsParents);
			SqlUtil.ClosePreparedStatement(psSelectChildren);
			SqlUtil.CloseResultSet(rsChildren);			
		}
	}
	
	
	private void initGUI() {
		try {
			
			ResourceBundle res_strings = ResourceBundle.getBundle("strings", Locale.getDefault(),new UTF8Control());
			
			{
				this.setPreferredSize(new java.awt.Dimension(394, 325));
				this.setLayout(null);
				{
					ctlPTSBook = new JComboBox();
					this.add(ctlPTSBook);
					ctlPTSBook.setBounds(93, 7, 289, 22);
					ctlPTSBook.addActionListener(new ActionListener() {

						@Override
						public void actionPerformed(ActionEvent arg0) {
							ctlPTSBookSelected(arg0);							
						}						
					});
				}
				{

					ctlVolume = new JComboBox();
					this.add(ctlVolume);
					ctlVolume.setBounds(93, 91, 64, 22);
					ctlVolume.addActionListener(new ActionListener() {

						@Override
						public void actionPerformed(ActionEvent arg0) {
							ctlVolumeSelected(arg0);							
						}						
					});
				}
				{
					ctlVolumeLabel = new JLabel();
					this.add(ctlVolumeLabel);
					ctlVolumeLabel.setText(res_strings.getString("Volume"));
					ctlVolumeLabel.setBounds(12, 95, 76, 15);
				}
				{
					ctlPageLabel = new JLabel();
					this.add(ctlPageLabel);
					ctlPageLabel.setText(res_strings.getString("Page"));
					ctlPageLabel.setBounds(188, 95, 122, 15);
				}
				{
					ctlPageNumber = new JSpinner();
					this.add(ctlPageNumber);

					ctlPageNumber.setBounds(310, 92, 72, 22);
				}
				{
					ctlPTSBookLabel = new JLabel();
					this.add(ctlPTSBookLabel);
					ctlPTSBookLabel.setText(res_strings.getString("PTSbook"));
					ctlPTSBookLabel.setBounds(6, 7, 82, 15);
				}
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
	}

	private void ctlPTSBookSelected(ActionEvent arg0)
	{
		CollectionInfo objCollection=(CollectionInfo)ctlPTSBook.getSelectedItem();
		if(objCollection==null || !objCollection.hasPTSPages())
		{
			ctlVolume.removeAllItems();
			ctlVolume.setEnabled(false);
			ctlPageNumber.setEnabled(false);
		}
		else
		{
			ctlVolume.setEnabled(true);
						
			ResultSet rs = null;
	        PreparedStatement ps = null;
			
			try
			{				
				ps=	MainWindow.getDBConnection().prepareStatement("SELECT PTSPages.VolumeID AS VolumeID, MIN(PTSPages.PageMin) AS MinPage, MAX(PTSPages.PageMax) AS MaxPage "+
					"FROM PTSPages INNER JOIN "+
					"Files ON PTSPages.FileID = Files.FileID "+
					"WHERE (Files.CollectionID = ?) "+
					"GROUP BY PTSPages.VolumeID "+
					"ORDER BY PTSPages.VolumeID ");
				ps.setInt(1, objCollection.getCollectionID());
				
				rs=ps.executeQuery();
				ArrayList<VolumeInfo> arrVolumes=new ArrayList<VolumeInfo>();
				while(rs.next())
				{
					arrVolumes.add(new VolumeInfo(rs.getInt("VolumeID"),rs.getInt("MinPage"),rs.getInt("MaxPage")));
				}		
				
				ComboBoxModel ctlVolumeModel = new DefaultComboBoxModel(arrVolumes.toArray());
				ctlVolume.setModel(ctlVolumeModel);
				ctlVolume.setSelectedIndex(0);
				ctlPageNumber.setEnabled(true);
			}
			catch(SQLException sqle)
			{		
				SqlUtil.ReportSQLException(sqle);
			}
			catch(Exception ex)
			{		
				ex.printStackTrace();					
			}
			finally
			{
				SqlUtil.ClosePreparedStatement(ps);
				SqlUtil.CloseResultSet(rs);		
			}
		}
	}
	
	private void ctlVolumeSelected(ActionEvent arg0)
	{
		VolumeInfo objVolume=(VolumeInfo)ctlVolume.getSelectedItem();
		SpinnerNumberModel ctlPageNumberModel = 
			new SpinnerNumberModel(objVolume.getMinPage(), objVolume.getMinPage(), objVolume.getMaxPage(), 1);

		ctlPageNumber.setModel(ctlPageNumberModel);
	}
	
	public int getNodeID()
	{
		int NodeID=0;
		
		if(_NodeID>0)
			NodeID=_NodeID;
		else
		{			
			VolumeInfo objVolume=(VolumeInfo)ctlVolume.getSelectedItem();
	
			if(objVolume!=null)
			{
				ResultSet rs = null;
		        PreparedStatement ps = null;
		        
				SpinnerNumberModel ctlPageNumberModel = (SpinnerNumberModel)ctlPageNumber.getModel();
		        
				try
				{
					ps=	MainWindow.getDBConnection().prepareStatement("SELECT nodes.NodeID "+
						"FROM PTSPages INNER JOIN Files ON PTSPages.FileID = Files.FileID INNER JOIN nodes ON PTSPages.FileID=nodes.FileID "+
						"WHERE (PTSPages.VolumeID = ?) AND (? BETWEEN PTSPages.PageMin AND PTSPages.PageMax) AND (Files.CollectionID = ?) ORDER BY nodes.NodeID "+
						"FETCH FIRST ROW ONLY");
					
					ps.setInt(1, objVolume.getVolumeID());
					ps.setInt(2, ctlPageNumberModel.getNumber().intValue());
					ps.setInt(3, ((CollectionInfo)ctlPTSBook.getSelectedItem()).getCollectionID());
					rs=ps.executeQuery();
					if(rs.next())
					{
						NodeID=rs.getInt("nodeid");
					}			
				}
				catch(SQLException sqle)
				{		
					SqlUtil.ReportSQLException(sqle);
				}
				catch(Exception ex)
				{		
					ex.printStackTrace();					
				}
				finally
				{
					SqlUtil.ClosePreparedStatement(ps);
					SqlUtil.CloseResultSet(rs);	
				}
			}
		}
		
		return NodeID;
	}
	
	public String getPTSPage()
	{
		VolumeInfo objVolume=(VolumeInfo)ctlVolume.getSelectedItem();
		SpinnerNumberModel ctlPageNumberModel = (SpinnerNumberModel)ctlPageNumber.getModel();
		return String.format("%d.%04d",objVolume.getVolumeID(),ctlPageNumberModel.getNumber().intValue());
	}
	

	private class VolumeInfo
	{
		private int _VolumeID;
		private int _MinPage;
		private int _MaxPage;
		
		
		public VolumeInfo(int VolumeID,int MinPage,int MaxPage)
		{
			_VolumeID=VolumeID;
			_MinPage=MinPage;
			_MaxPage=MaxPage;
		}
		
		public String toString()
		{
			return String.valueOf(_VolumeID);
		}
		
		public int getMinPage()
		{
			return _MinPage;
		}
		
		public int getMaxPage()
		{
			return _MaxPage;
		}		
		
		public int getVolumeID()
		{
			return _VolumeID;
		}
	}
}
